package com.etc.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.etc.dao.IArticleDao;
import com.etc.entity.Article;
import com.etc.util.DBUtil;
import com.etc.util.PageData;

public class ArticleDaoImpl implements IArticleDao {

	@Override
	public List<Article> queryArticles() {
		Article article=null;
		List<Article> list =new  ArrayList<Article>();
		CachedRowSet crs = DBUtil.execQuery("select * from article");
		try {
			while(crs.next()) {
				int articleId = crs.getInt(1);
				String articleTitle = crs.getString(2);
				String articleContent = crs.getString(3);
				String articleAuthor = crs.getString(4);
				String articleDate = crs.getString(5);
				article=new  Article(articleId, articleTitle, articleContent, articleAuthor, articleDate);
			    list.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean addArticle(Article article) {
		
		return DBUtil.executeUpdate("insert into article values(null,?,?,?,now())", article.getArticleTitle(),article.getArticleContent(),article.getArticleAuthor());
	}

	@Override
	public boolean delArticle(int articleId) {
		return DBUtil.executeUpdate("delete from article where articleId=? ", articleId);
	}

	@Override
	public boolean updateArticle(Article article) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("update article set articleTitle=?,articleContent=?,articleAuthor=?,articleDate=now() where articleId=?", article.getArticleTitle(),article.getArticleContent(),article.getArticleAuthor(),article.getArticleId());
	}

	@Override
	public List<Article> queryArticleByLike(String articleLike) {
		Article article=null;
		List<Article> list =new  ArrayList<Article>();
		CachedRowSet crs = DBUtil.execQuery("select * from article where articleTitle like ? or articleContent=?","%"+articleLike+"%","%"+articleLike+"%");
		try {
			while(crs.next()) {
				int articleId = crs.getInt(1);
				String articleTitle = crs.getString(2);
				String articleContent = crs.getString(3);
				String articleAuthor = crs.getString(4);
				String articleDate = crs.getString(5);
				article=new  Article(articleId, articleTitle, articleContent, articleAuthor, articleDate);
			    list.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}

	@Override
	public List<Article> queryTopArticles() {
		Article article=null;
		List<Article> list =new  ArrayList<Article>();
		CachedRowSet crs = DBUtil.execQuery("select * from article order by articleDate desc limit 0,3 ");
		try {
			while(crs.next()) {
				int articleId = crs.getInt(1);
				String articleTitle = crs.getString(2);
				String articleContent = crs.getString(3);
				String articleAuthor = crs.getString(4);
				String articleDate = crs.getString(5);
				article=new  Article(articleId, articleTitle, articleContent, articleAuthor, articleDate);
			    list.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Article queryArticleById(int articleId) {
		Article article=null;
		CachedRowSet crs = DBUtil.execQuery("select * from article where articleId=?",articleId);
		try {
			while(crs.next()) {
				String articleTitle = crs.getString(2);
				String articleContent = crs.getString(3);
				String articleAuthor = crs.getString(4);
				String articleDate = crs.getString(5);
				article=new  Article(articleTitle, articleContent, articleAuthor, articleDate);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return article;
	}

	@Override
	public List<Article> seachByTitle(String title) {
		Article article=null;
		List<Article> list =new  ArrayList<Article>();
		CachedRowSet crs = DBUtil.execQuery("select * from article where articleTitle like ? order by articleDate desc limit 0,3 ","%"+title+"%");
		try {
			while(crs.next()) {
				int articleId = crs.getInt(1);
				String articleTitle = crs.getString(2);
				String articleContent = crs.getString(3);
				String articleAuthor = crs.getString(4);
				String articleDate = crs.getString(5);
				article=new  Article(articleId, articleTitle, articleContent, articleAuthor, articleDate);
			    list.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public PageData queryArticleByLikePage(int pageNo, int pageSize, String titleKey) {
		//1.获取分页的数据
		String sql="select * from article where articleTitle like ? LIMIT ?,?";
		CachedRowSet crs = DBUtil.execQuery(sql, "%"+titleKey+"%",(pageNo-1)*pageSize,pageSize);
		Article article=null;
		List<Article> list =new  ArrayList<Article>();
		try {
			while(crs.next()) {
				int articleId = crs.getInt(1);
				String articleTitle = crs.getString(2);
				String articleContent = crs.getString(3);
				String articleAuthor = crs.getString(4);
				String articleDate = crs.getString(5);
				article=new  Article(articleId, articleTitle, articleContent, articleAuthor, articleDate);
			    list.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//2.获取分页的总条数
		int totalCount=0;
		String sql2="select count(*) from article where articleTitle like ?";
		CachedRowSet crs2 = DBUtil.execQuery(sql2, "%"+titleKey+"%");
		try {
			while(crs2.next()) {
				totalCount = crs2.getInt(1);
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		PageData pageData = new PageData(list, pageNo, pageSize, totalCount);
		 return pageData;
	}

}
